package code14JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 黄衡熙
 * @version 1.0
 */
/*
*创建一张学生表,拥有以下字段:主键id、学号no唯一且不为空、姓名不为空、年龄
* 完成以下方法
* 1.添加学生信息
* 2.根据学号删除指定学生信息
* 3.根据学号修改指定学生信息
* 4.查询所有学生信息
* 5.根据学号查询指定学生信息
* 6.根据姓名进行模糊查询
* 7.根据年龄范围查询指定学生信息*/
public class Test_练习 {
    public static void main(String[] args) {
        /*Student student = new Student("9","小红",16);
        addStudent(student);*/

       // deleteById("2");

        /*Student student = new Student("jack",22);
        student.setNo("3");
        updateStudent(student);*/

       /* List<Student> students = selectAll();
       for (Student s : students) {
           System.out.println(s);
       }*/

        //System.out.println( selectByNo("1"));

       /* List<Student> student = selectByUsername("小");
        for (Student s : student) {
            System.out.println(s);
        }*/


        List<Student> student =selectByAge(15,19);
        for (Student s : student) {
            System.out.println(s);
        }



    }
    //添加学生信息
    public static void addStudent(Student student) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "insert into t_student(no,username,age) values(?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getNo());
            ps.setString(2, student.getUsername());
            ps.setInt(3, student.getAge());
            ps.executeUpdate();
            System.out.println("提示:恭喜您,添加成功");
        } catch (SQLException e) {
            throw new DataAccessException("数据访问异常");
        } finally {
            // 对于不需要释放的资源,其值直接给null即可
            JDBCUtil.close(conn, ps, null);
        }
    }
    //.根据学号删除指定学生信息
    public static void deleteById(String no) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "delete from t_student where no=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, no);
            ps.executeUpdate();
            System.out.println("提示:恭喜您,删除成功");
        } catch (SQLException e) {
            throw new RuntimeException("数据访问异常");
        }finally {
            JDBCUtil.close(conn, ps, null);
        }
    }
    //根据学号修改指定学生信息
    public static void updateStudent(Student student) {
        Connection conn = null;
        PreparedStatement ps = null;
           try{
               conn = JDBCUtil.getConnection();
               String sql = "update t_student set username=?,age=? where no=?";
               ps=conn.prepareStatement(sql);
               ps.setString(1, student.getUsername());
               ps.setInt(2, student.getAge());
               ps.setString(3, student.getNo());
               ps.executeUpdate();
               System.out.println("修改成功");
           } catch (SQLException e) {
               throw new RuntimeException("数据访问异常");
           }finally {
               JDBCUtil.close(conn, ps, null);
           }
    }
    //4.查询所有学生信息
    public static List<Student> selectAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select id, no, username, age from t_student";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setUsername(rs.getString("username"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (SQLException e) {
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }
//5.根据学号查询指定学生信息
public static Student selectByNo(String no) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Student student = null;
    try{
        conn = JDBCUtil.getConnection();
        String sql = "select id, no, username, age from t_student where no=?";
        ps=conn.prepareStatement(sql);
        ps.setString(1, no);
        rs = ps.executeQuery();
        while (rs.next()) {
            student = new Student();
            student.setId(rs.getInt("id"));
            student.setNo(rs.getString("no"));
            student.setUsername(rs.getString("username"));
            student.setAge(rs.getInt("age"));
        }
    } catch (SQLException e) {
        throw new RuntimeException("数据访问异常");
    }finally {
        JDBCUtil.close(conn, ps, rs);
    }
    return student;
}

//6.根据姓名进行模糊查询
    public static List<Student> selectByUsername(String username) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try{
            conn = JDBCUtil.getConnection();
            String sql = "select id, no, username, age from t_student where username like ?";
            ps=conn.prepareStatement(sql);
            ps.setString(1, "%"+username+"%");
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setUsername(rs.getString("username"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException("访问数据库异常");
        }finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }
    //7.根据年龄范围查询指定学生信息
    public static List<Student> selectByAge(Integer age1,Integer age2) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try{
            conn = JDBCUtil.getConnection();
            String sql = "select id, no, username, age from t_student where age between ? and ?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1, age1);
            ps.setInt(2, age2);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setUsername(rs.getString("username"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException("访问数据库异常");
        }finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }
}
